﻿SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/***************************************************
 ****************** create tables ******************
 ***************************************************/

/****** Newsletter ******/
CREATE TABLE [dbo].[NL_Newsletter](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ID_Template] [int] NULL,
	[Subject] [nvarchar](50) NULL,
	[Date] [datetime] NULL,
	[Send] [bit] NULL,
 CONSTRAINT [PK_NL_Newsletter_1] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

/****** Subscriber ******/
CREATE TABLE [dbo].[NL_Subscriber](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[IsAuthenticated] [uniqueidentifier] NULL,
	[FirstName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL,
	[Titel] [nvarchar](50) NULL,
	[Salutation] [nvarchar](50) NULL,
	[Email] [nvarchar](50) NULL,
	[City] [nvarchar](50) NULL,
	[ZIP] [nvarchar](50) NULL,
	[Street] [nvarchar](50) NULL,
	[Language] [nvarchar](50) NULL,
	[Valid] [bit] NULL,
	[Date] [datetime] NULL,
 CONSTRAINT [PK_NL_Subscriber_1] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

/****** Settings ******/
CREATE TABLE [dbo].[NL_Settings](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ID_Template] [uniqueidentifier] NULL,
	[Sender] [nvarchar](50) NULL,
	[Test] [bit] NULL,
 CONSTRAINT [PK_NL_Settings_1] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

/****** Template ******/
CREATE TABLE [dbo].[NL_Template](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
	[Template_Content] [nvarchar](max) NULL,
 CONSTRAINT [PK_NL_Template_1] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

/****** Subscriber_Newsletter ******/
CREATE TABLE [dbo].[NL_Subscriber_Newsletter](
	[ID_Subscriber] [int] NOT NULL,
	[ID_Newsletter] [int] NOT NULL,
 CONSTRAINT [PK_NL_Subscriber_Newsletter] PRIMARY KEY CLUSTERED 
(
	[ID_Subscriber] ASC,
	[ID_Newsletter] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

/****** Newsletter_Text ******/
CREATE TABLE [dbo].[NL_Newsletter_Text](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ID_NewsLetter] [int] NULL,
	[Newsletter_Content] [nvarchar](max) NULL,
 CONSTRAINT [PK_NL_Newsletter_Text_1] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

/***************************************************
 ******************* foreign keys ******************
 ***************************************************/

ALTER TABLE [dbo].[NL_Newsletter_Text]  WITH CHECK ADD  CONSTRAINT [FK_NL_Newsletter_Text_NL_Newsletter] FOREIGN KEY([ID_NewsLetter])
REFERENCES [dbo].[NL_Newsletter] ([ID])
GO

ALTER TABLE [dbo].[NL_Subscriber_Newsletter]  WITH CHECK ADD  CONSTRAINT [FK_NL_Subscriber_Newsletter_NL_Newsletter] FOREIGN KEY([ID_Newsletter])
REFERENCES [dbo].[NL_Newsletter] ([ID])
GO

ALTER TABLE [dbo].[NL_Subscriber_Newsletter]  WITH CHECK ADD  CONSTRAINT [FK_NL_Subscriber_Newsletter_NL_Subscriber] FOREIGN KEY([ID_Subscriber])
REFERENCES [dbo].[NL_Subscriber] ([ID])
GO

ALTER TABLE [dbo].[NL_Newsletter]  WITH CHECK ADD  CONSTRAINT [FK_NL_Newsletter_NL_Template] FOREIGN KEY([ID_Template])
REFERENCES [dbo].[NL_Template] ([ID])
GO

/***************************************************
 **************** check constraints ****************
 ***************************************************/

ALTER TABLE [dbo].[NL_Newsletter_Text] CHECK CONSTRAINT [FK_NL_Newsletter_Text_NL_Newsletter]
GO

ALTER TABLE [dbo].[NL_Subscriber_Newsletter] CHECK CONSTRAINT [FK_NL_Subscriber_Newsletter_NL_Newsletter]
GO

ALTER TABLE [dbo].[NL_Subscriber_Newsletter] CHECK CONSTRAINT [FK_NL_Subscriber_Newsletter_NL_Subscriber]
GO

ALTER TABLE [dbo].[NL_Newsletter] CHECK CONSTRAINT [FK_NL_Newsletter_NL_Template]
GO


